import configparser
from sqlalchemy import create_engine
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.font_manager as fm
import matplotlib.dates as mdates
from influxdb_client import InfluxDBClient

# 创建 ConfigParser 对象
config = configparser.ConfigParser()

# 读取配置文件
config.read('config.ini')

# 读取 数据库配置
db_host = config['mysqldb']['host']
db_user = config['mysqldb']['user']
db_password = config['mysqldb']['password'].replace('@', '%40')
db_name = config['mysqldb']['dbname']

# 读取 InfluxDB配置
bucket = config['influxdb']['bucket']

# 示例输出
print(f"mysql+mysqlconnector://{db_user}:{db_password}@{db_host}:13506/{db_name}")
print(f"API bucket: {bucket}")


# 设置字体
font_path = "/System/Library/Fonts/PingFang.ttc"  # 替换为中文字体路径
my_font = fm.FontProperties(fname=font_path)
plt.rcParams['font.family'] = my_font.get_name()  # 用于支持中文标签
plt.rcParams['axes.unicode_minus'] = False    # 解决负号显示问题


# 创建 MySQL SQLAlchemy 引擎
mysql_url = f"mysql+mysqlconnector://{db_user}:{db_password}@{db_host}:13506/{db_name}"
engine = create_engine(mysql_url)

# InfluxDB 连接设置
influx_client = InfluxDBClient(
    url=config['influxdb']['url'],  # InfluxDB 的 URL
    token=config['influxdb']['token'],
    org=config['influxdb']['org']
)


# 从 MySQL 获取比赛基本信息
mysql_query = "SELECT m.game_name,w.match_id, w.team1_id, w.team2_id, w.winner ,w.status FROM vote_raybet_winner w , vote_raybet_match m  where w.match_fork = m.id and w.match_id in ('38037461-r1-','38037461-r2-','38037895-final-','38037931-final-','38037931-map1-','38037931-map2-','38037971-final-','38037974-final-','38038115-final-','38038800-final-K','38038803-final-','38038932-r1-','38038932-r2-','38039182-final-L','38039185-final-L','38039281-final-','38039296-final-','38039786-r1-','38039786-r2-','38039801-final-','38039979-final-','38040969-r1-T','38042577-final-','38042577-r1-','38042577-r2-','38042586-final-','38043452-final-','38043895-final-P','38043895-r2-P','38044656-map1-','38044700-final-','38044742-final-','38044772-final-','38044808-final-','38044941-final-','38044990-final-','38045662-final-','38045665-final-','38045683-final-','38045686-r1-')"
# mysql_query = "SELECT m.game_name,w.match_id, w.team1_id, w.team2_id, w.winner FROM vote_raybet_winner w , vote_raybet_match m  where w.match_fork = m.id and now()>m.end_time  and m.id='38034378'"
#mysql_query = "SELECT m.game_name,w.match_id, w.team1_id, w.team2_id, w.winner ,w.status FROM vote_raybet_winner w , vote_raybet_match m  where w.match_fork = m.id and  now()>m.end_time and m.start_time >  STR_TO_DATE('2024-09-15', '%Y-%m-%d') and m.game_name = 'CS2'"


match_info_df = pd.read_sql(mysql_query, engine)  # 使用 SQLAlchemy 引擎





# 查询 InfluxDB 赔率数据的函数
def query_odds_from_influxdb(team_id):
    query = f'''
    from(bucket: "{bucket}")
    |> range(start: 0)
    |> filter(fn: (r) => r["_measurement"] == "odds")
    |> filter(fn: (r) => r["_field"] == "odds")
    |> filter(fn: (r) => r["team"] == "{team_id}")
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> sort(columns: ["_time"], desc: false)
    |> yield(name: "all_data")
    '''
    
    # print(f"-----------------")
    # print(query)
    # print(f"=================")
    result = influx_client.query_api().query_data_frame(query)
    # 去掉前5个数据点
    #if not result.empty and len(result) > 10:
    #    result = result.iloc[3:-1]    

        # 打印所有数据到 CSV
    # if not result.empty:
    #     # 保存所有数据到 CSV 文件
    #     csv_filename = f"../matplotlib/team_{team_id}_odds_data.csv"
    #     result.to_csv(csv_filename, index=False)
    #     print(f"数据已保存到 {csv_filename}")

    return result if not result.empty else None


# 数据预处理
def preprocess_data(df):
    if df is not None:
        # 将时间列转换为 datetime 对象
        df['_time'] = pd.to_datetime(df['_time'])
        
        # 设定时间列为索引
        df.set_index('_time', inplace=True)
        
        # 计算时间间隔
        df['time_diff'] = df.index.to_series().diff().dt.total_seconds()
        
        # 找到时间间隔较长的点
        avg_interval = df['time_diff'].mean()
        long_interval_threshold = 5 * avg_interval  # 可以根据数据调整阈值
        
        # 估算比赛开始时间（时间间隔最长的点）
        potential_start_time = df[df['time_diff'] > long_interval_threshold].index.min()
        
        if pd.isna(potential_start_time):
            potential_start_time = df.index.min()  # 如果没有明显的长间隔点，使用最早时间

        # 过滤掉比赛开始前的数据
        df = df[df.index >= potential_start_time]

        # 只选择数值型列进行重采样
        numeric_df = df.select_dtypes(include=[np.number])
        
        # 重采样数据到秒级别
        df_resampled = numeric_df.resample('100s').mean()
        
        # 填补缺失数据
        df_resampled.interpolate(method='linear', inplace=True)
        
        return df_resampled.reset_index()
    return None



# 为每场比赛生成图表并保存
for index, row in match_info_df.iterrows():
    game_name = row['game_name']
    match_id = row['match_id']
    team1_id = row['team1_id']
    team2_id = row['team2_id']
    winner = row['winner']
    status = row['status']
    
    # 从 InfluxDB 获取 team1 和 team2 的赔率数据
    team1_odds_df = query_odds_from_influxdb(team1_id)
    team2_odds_df = query_odds_from_influxdb(team2_id)

    # team1_odds_df = preprocess_data(team1_odds_df)
    # team2_odds_df = preprocess_data(team2_odds_df)
    # 如果两个队伍的赔率数据都存在，生成图表
    if team1_odds_df is not None and team2_odds_df is not None:
        plt.figure(figsize=(10, 6))

        # 绘制 team1 赔率
        plt.plot(pd.to_datetime(team1_odds_df['_time']), team1_odds_df['odds'], label=f'{team1_id}-1-{len(team1_odds_df)}')

        # 绘制 team2 赔率
        plt.plot(pd.to_datetime(team2_odds_df['_time']), team2_odds_df['odds'], label=f'{team2_id}-2-{len(team2_odds_df)}')

        # 图表设置
        plt.title(f'ID: {match_id} - 趋势图 - {status}' )
        plt.xlabel('时间')
        plt.ylabel('值')
        plt.legend()
        plt.grid(True)

        # 设置时间格式
        plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d %H:%M:%S'))
        # plt.gca().xaxis.set_major_locator(mdates.SecondLocator(interval=100))  # 每秒一个刻度

        # 用于自动旋转 x 轴的日期标签，以确保它们不会重叠。
        plt.gcf().autofmt_xdate() 

        # plt.xticks(rotation=45)
        plt.tight_layout()
        
        # 保存图表
        plt.savefig(f'../matplotlib/{game_name}-{match_id}{winner}.png')

        # 清除当前图表
        plt.close()


                # 合并两个 DataFrame，基于时间列 '_time'，使用内连接以确保时间匹配
        merged_df = pd.merge(team1_odds_df[['odds', '_time']], 
                             team2_odds_df[['odds', '_time']], 
                             on='_time', 
                             suffixes=('_team1', '_team2'))

        # 重命名列，以便更好地理解
        merged_df.rename(columns={'_time': 'time', 'odds_team1': f'{team1_id}_odds', 'odds_team2': f'{team2_id}_odds'}, inplace=True)

        # 输出 CSV 文件
        csv_file_path = f'../csv_files/{game_name}-{match_id}-odds.csv'
        merged_df.to_csv(csv_file_path, index=False)

        print(f'CSV 文件已生成: {csv_file_path}')

# 关闭 InfluxDB 连接
influx_client.close()

print("所有比赛的赔率图表生成完毕并保存。")



# 鞠主任，我们下午又更新啦一版本算法。 
# 另外SDC是不是有半年的数据这个可以导出来，这个看看怎么弄出来？